package me.zhengjie.modules.gu_excel.service;

import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import me.zhengjie.modules.gu_excel.entity.DataExcelBase;
import me.zhengjie.modules.gu_excel.entity.DataExcelInfo;
import me.zhengjie.modules.gu_excel.mapper.BaseMapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @Author: GuSongLiao
 * @Date: 2024/2/23 17:27
 * @Description: 上传excel service
 */

@Service
@Slf4j
@AllArgsConstructor
public class HandleExcelService {

    @Resource
    private DataExcelBaseService dataExcelBaseService;

    @Resource
    private DataExcelInfoService dataExcelInfoService;

    @Resource
    private BaseMapper baseMapper;

    @Transactional
    public void uploadExcel(MultipartFile file) throws IOException {

        // 获取上传文件的输入流
        InputStream inputStream = file.getInputStream();
        // 创建工作簿对象
        Workbook workbook = new XSSFWorkbook(inputStream);
        // 获取第一个工作表
        Sheet sheet = workbook.getSheetAt(0);

        List<DataExcelInfo> dataExcelInfoList = new ArrayList<>();
        DataExcelBase dataExcelBase = new DataExcelBase();
        dataExcelBase.setFileName(file.getOriginalFilename());
        dataExcelBaseService.save(dataExcelBase);
        log.info("dataExcelBase id:{}", dataExcelBase.getId());

        // 遍历工作表的每一行
        for (Row row : sheet) {
            // 遍历每一行的每一个单元格
            for (Cell cell : row) {
                DataExcelInfo dataExcelInfo = new DataExcelInfo();
                cell.setCellType(CellType.STRING);
                String value = cell.getStringCellValue();

                dataExcelInfo.setCellType("String");
                dataExcelInfo.setExcelId(dataExcelBase.getId());
                dataExcelInfo.setCellValue(value);
                dataExcelInfo.setRowValue(row.getRowNum());
                dataExcelInfo.setColValue(cell.getColumnIndex());
                dataExcelInfo.setCreatetime(new Date());
                dataExcelInfo.setUpdatetime(new Date());
                dataExcelInfoList.add(dataExcelInfo);
            }
        }

        dataExcelInfoService.saveBatch(dataExcelInfoList);

        log.info("{}上传成功", file.getOriginalFilename());

        // 关闭工作簿和输入流
        workbook.close();
        inputStream.close();
    }

    public List<Map<String, String>> getExcelInfo(int excelId) {

        QueryWrapper<DataExcelInfo> queryWrapper = new QueryWrapper();
        queryWrapper.select(" DISTINCT col_value ").lambda()
                .eq(DataExcelInfo::getExcelId, excelId);
        int count = (int) dataExcelInfoService.count(queryWrapper);
        log.info("excel_id={},共有{}列",excelId, count);


        StringBuilder sql = new StringBuilder("SELECT ");
        String result = "";
        if (count > 0) {
            for (int i = 0; i < count; i++) {
                sql.append("MAX(CASE WHEN col_value = '")
                        .append(i)
                        .append("' THEN cell_value END) AS ")
                        .append("col_").append(i)
                        .append(",");
            }
            result = sql.substring(0, sql.length() - 1);
            result += " FROM data_excel_info WHERE excel_id = \n" + excelId +
                    " GROUP BY row_value ORDER BY row_value;";

            List<Map<String, String>> list = baseMapper.nativeSql(result);

            log.info("查询结果为, {}", JSON.toJSONString(list));

            return list;
        }

        return new ArrayList<>();

    }

    public List<DataExcelBase> getExcelDataBase() {
        return dataExcelBaseService.list();
    }

    public void updateExcelHead(int excelId, int headId) {
        DataExcelBase dataExcelBase = new DataExcelBase();
        dataExcelBase.setId(excelId);
        dataExcelBase.setHeadRow(headId);
        dataExcelBaseService.updateById(dataExcelBase);
    }

    public Integer getExcelHead(int excelId) {
        DataExcelBase byId = dataExcelBaseService.getById(excelId);
        return byId.getHeadRow();
    }
}
